/*
 * db_storage.c
 *
 *  Created on: 9-dic-2008
 *      Author: igor
 */

#include "storage.h"

/* Display result of a query */
static int callback(void *NotUsed, int argc, char **argv, char **azColName)
{
   printf("DEBUG: callback;\n");
    int i = 0;

    /* Iterate throw result rows */
    for(i=0; i<argc; i++)
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    printf ("\n");

    return (EXIT_SUCCESS);
}

/* verifica l'esistenza di una tabella nel database */
static int
db_table_verify (sqlite3 *db, char *tbname)
{
   printf ("DEBUG <db_table_verify>\n");

   char query[512];
   char *errMsg = 0; /* pointer to error message */

   char **pazResult = NULL;    /* Results of the query */
   int pnRow = 0;				/* Number of result rows written here */
   int pnColumn = 0;			/* Number of result columns written here */

   sprintf(query, "SELECT name FROM sqlite_master WHERE type='table' ORDER by name");
   printf ("DEBUG query: %s\n", query);

   if (sqlite3_get_table(db, query, &pazResult, &pnRow, &pnColumn, &errMsg) != SQLITE_OK) {
     fprintf(stderr, "SQL error: %s\n", errMsg);
     sqlite3_free(errMsg);
     return (EXIT_FAILURE);
   }

   int i = 0;
   int m = pnRow;

   for (i = 0; i <= pnRow; i++) {

	   if (strncmp(pazResult[i], tbname, strlen(tbname)) == 0) {
		   printf("DEBUG: tabella %s trovata;\n", tbname);
		   return (EXIT_SUCCESS);
	   }

   }

   printf("DEBUG: tabella %s NON trovata;\n", tbname);
   return (EXIT_FAILURE);
}

/* Open database, and create table if necessary */
sqlite3
*open_db ()
{
   printf("DEBUG: open_db;\n");
    int n = 0, exist = 0;

    sqlite3 *db;
    char *errMsg = 0; /* pointer to error message */

    FILE *fp;

   /* controlla se il file viene aperto */
   if ((fp = fopen(dbname, "r")) == NULL)
   {
      printf("Non posso aprire il file %s\n", dbname);

      if (mkdir(pathname, S_IRWXU | S_IRWXG | S_IROTH | S_IXOTH) != 0) // http://digilander.libero.it/uzappi/C/librerie/funzioni/mkdir.html
   	  /* errore durante la creazione della directory */
          printf("Errore durante la creazione della directory %s\n", pathname);
         if (fopen(dbname, "w") == NULL) {
        	 printf("Impossibile creare il file.\n");
	        exit(1);
         }
   } else {
	   printf ("file %s esists;\n", dbname);
	   exist = 1;
	   printf ("exist equal %d\n", exist);
   }


    /* Check whether the database file existence, and flag it */
    /* Open database file, or create it  */
    if (sqlite3_open (dbname, &db)) {
        fprintf (stderr, "Can't open database: %s\n",
            sqlite3_errmsg (db));
        sqlite3_close (db);
        return NULL;
    }

    /* New database, so create required table */
    if ((exist == 0) || db_table_verify(db, "data") != EXIT_SUCCESS) {
	   char query[512];
	   sprintf(query, "CREATE TABLE IF NOT EXISTS data (title VARCHAR(256), infos VARCHAR(256) primary key, icon VARCHAR(256), text VARCHAR(1024))", "data");
	   printf ("DEBUG query: %s\n", query);
//        n = sqlite3_exec(db, query, NULL, 0, &errMsg);
       if ( sqlite3_exec(db, query, NULL, 0, &errMsg) != SQLITE_OK) {
          fprintf(stderr, "SQL error: %s\n", errMsg);
          return NULL;
       }
       printf("DEBUG: creata tabella data;\n");
    }

    if ((exist == 0) || db_table_verify(db, "cat_item_index") != EXIT_SUCCESS) {
 	   char query[512];
 	   sqlite3_snprintf (1024,query, "CREATE TABLE IF NOT EXISTS cat_item_index (catid INTEGER NOT NULL, iteminfos VARCHAR(256) NOT NULL)");
 	   printf ("DEBUG query: %s\n", query);

       if ( sqlite3_exec(db, query, NULL, 0, &errMsg) != SQLITE_OK) {
           fprintf(stderr, "SQL error: %s\n", errMsg);
           return NULL;
        }
        printf("DEBUG: creata tabella cat_item_index;\n");
    }

    if ((exist == 0) || db_table_verify(db, "list") != EXIT_SUCCESS) {
 	   char query[512];
 	   sqlite3_snprintf (1024,query, "CREATE TABLE IF NOT EXISTS list (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(1024) UNIQUE NOT NULL)");
 	   printf ("DEBUG query: %s\n", query);

       if ( sqlite3_exec(db, query, NULL, 0, &errMsg) != SQLITE_OK) {
          fprintf(stderr, "SQL error: %s\n", errMsg);
          return NULL;
       }

       sqlite3_snprintf (1024, query, "INSERT INTO list VALUES (NULL,'%q')", catname);
 	   printf ("DEBUG query: %s\n", query);

       if ( sqlite3_exec(db, query, NULL, 0, &errMsg) != SQLITE_OK) {
          fprintf(stderr, "SQL error: %s\n", errMsg);
          return NULL;
       }

       printf("DEBUG: creata tabella list;\n");
    }

    return db;
}

/*
 * leggo gli item dal database
 */
struct todoitem
*leggi_items_da_db(struct todoitem *p)
{

   printf("DEBUG: <leggi_items_da_db>\n");

   // vars
   t_todoitem daInserire;
   t_todoitem vuoto;
   struct todoitem *punt = p;

   sqlite3 *db = open_db();
   char *errMsg = 0; /* pointer to error message */
   char *query = calloc (1, 2048);

   if (db == NULL) exit(1);

   char **pazResult = NULL;    /* Results of the query */
   int pnRow = 0;           /* Number of result rows written here */
   int pnColumn = 0;        /* Number of result columns written here */

   /* Insert into database */
//   sqlite3_snprintf (1024, query, "SELECT title,infos,icon,text FROM '%q'", tablename);
   sqlite3_snprintf (1024, query, "SELECT title,infos,icon,text FROM data d INNER JOIN cat_item_index i ON d.infos=i.iteminfos INNER JOIN list l ON l.id=i.catid WHERE l.name='%q'", catname);
   printf ("DEBUG query: %s\n", query);

   if (sqlite3_get_table(db, query, &pazResult, &pnRow, &pnColumn, &errMsg) != SQLITE_OK) {
     fprintf(stderr, "SQL error: %s\n", errMsg);
     sqlite3_free(errMsg);
   }

   int i = 0;
   int m = pnRow, n = pnColumn;
//   printf ("%d * %d = %d\n", m, n, m*n);

   if (m > 0)
   for (i = 0; i < (m*n)+4; i++) {
//	   printf ("%s %s %s %s\n", pazResult[i], pazResult[i++], pazResult[i++], pazResult[i++]);
      if (i > 3) {
	   strcpy(daInserire.title, pazResult[i]);
	   strcpy(daInserire.infos, pazResult[++i]);
	   strcpy(daInserire.icon, pazResult[++i]);
	   strcpy(daInserire.text, pazResult[++i]);

	   p = punt;
	   punt = (struct todoitem *)malloc(sizeof(struct todoitem));
           punt->inf = daInserire;
           punt->pun = p;

           // azzera il valore di daInserire
           daInserire = vuoto;

      } else {
	      printf("DEBUG:  %d %s;\n", i, pazResult[i]);
      }

   }

   sqlite3_close(db);
   printf("DEBUG: </leggi_items_da_db>\n");
   return (punt);

}


/*
 * function add_item_into_db adds a new item into db
 * aggiungo un item al db
 */
struct todoitem
*add_item_into_db(struct todoitem *tdi)
{

   printf("DEBUG: salvo su database la nota\n");
   /* allocate memory */
   char *title = calloc (1, 2048);
   char *icon = calloc (1, 2048);
   char *infos = calloc (1, 2048);
   char *text = calloc (1, 2048);

   /* set correct values to variables */
   sprintf(title, tdi->inf.title);
   sprintf(icon, tdi->inf.icon);
   sprintf(infos, tdi->inf.infos);
   sprintf(text, tdi->inf.text);

   sqlite3 *db = open_db();
   char *errMsg = 0; /* pointer to error message */

   char *query = calloc (1, 2048);

   if (db == NULL)
   {
      /* problemi di apertura del db */
      return;
   }

   while (tdi != NULL)
   {
      /* Insert to database data */
      sqlite3_snprintf (1024, query, "INSERT INTO data VALUES ('%q','%q','%q', '%q')", title, infos, icon, text);
      printf ("DEBUG query: %s ;\n", query);

      /* Check return result */
      if ( sqlite3_exec(db, query, NULL, 0, &errMsg) != SQLITE_OK) {
         fprintf(stderr, "SQL error: %s\n", errMsg);
	     return (struct todoitem *)NULL; /* failure */
      }

      /* Insert to database index */
      sqlite3_snprintf (1024, query, "INSERT INTO cat_item_index VALUES ((SELECT id FROM list WHERE name='%q'),'%q')", catname, infos);
      printf ("DEBUG query: %s ;\n", query);

      /* Check return result */
      if ( sqlite3_exec(db, query, NULL, 0, &errMsg) != SQLITE_OK) {
         fprintf(stderr, "SQL error: %s\n", errMsg);
	     return (struct todoitem *)NULL; /* failure */
      }

      tdi = tdi->pun;
   }

   printf ("DEBUG: salvata con successo su database;\n");

   return tdi;
}


/*
 * modifico un item nel db
 */
struct todoitem
*mod_item_into_db(struct todoitem *tdi)
{

   printf("DEBUG: modifico su database la nota\n");
   char *title = strdup(tdi->inf.title);

   /* allocate memory */
   char *icon = tdi->inf.icon;
   char *infos = tdi->inf.infos;
   char *text = strdup(tdi->inf.text);

   // Remove trailing "<br>"
   if(strlen(title) >= 4 && !strncmp(title + (strlen(title)-4), "<br>", 4)) {
       title[strlen(title) - 4] = '\0';
   }

   sqlite3 *db = open_db();
   char *errMsg = 0; /* pointer to error message */

   char *query = calloc (1, 2048);

   if (db == NULL)
   {
      /* problemi di apertura del db */
      return;
   }

   while (tdi != NULL)
   {
      /* Insert to database */
      sqlite3_snprintf (1024, query, "UPDATE %q SET title='%q', icon='%q', text='%q' WHERE infos='%q'", "data", title, icon, text, infos);
      printf ("DEBUG query: %s ;\n", query);

      /* Check return result */
      if ( sqlite3_exec(db, query, NULL, 0, &errMsg) != SQLITE_OK) {
         fprintf(stderr, "SQL error: %s\n", errMsg);
 	     return (struct todoitem *)NULL; /* failure */
      }

      tdi = tdi->pun;
   }

   printf ("DEBUG: modificata con successo su database;\n");

   return tdi;
}


/* delete an entry from db */
struct todoitem
*del_item_from_db(struct todoitem *data)
{

   printf("DEBUG; <del_item_from_db>\n");

   struct todoitem *tdi = data;
   printf ("DEBUG infos: %s;\n", tdi->inf.infos);

   sqlite3 *db = open_db();
   char *errMsg = 0; /* pointer to error message */

   char *query = calloc (1, 2048);
   sqlite3_snprintf (2048, query, "DELETE FROM %q WHERE infos='%q'", "data", tdi->inf.infos);
   printf ("query = %s\n", query);

   if (sqlite3_exec(db, query, callback, 0, &errMsg) != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", errMsg);
        return NULL;
    }

   sqlite3_snprintf (2048, query, "DELETE FROM %q WHERE infos='%q'", "cat_item_index", tdi->inf.infos);
   printf ("query = %s\n", query);

   if (sqlite3_exec(db, query, callback, 0, &errMsg) != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", errMsg);
        return NULL;
    }

   printf("DEBUG; </del_item_from_db>\n");
   return data;
}


/* aggiunge una categoria al database */
char
*add_cat_into_db(char *name)
{
	printf ("DEBUG: <add_cat_into_db>\n");

    sqlite3 *db = open_db();
    char *errMsg = 0; /* pointer to error message */
    char *query = calloc (1, 2048);

    if (db == NULL) exit(1);

    sqlite3_snprintf (1024, query, "INSERT INTO list VALUES (NULL,'%q')", name);
	   printf ("DEBUG query: %s\n", query);

    if ( sqlite3_exec(db, query, NULL, 0, &errMsg) != SQLITE_OK) {
       fprintf(stderr, "SQL error: %s\n", errMsg);
       return NULL;
    }

	printf ("DEBUG: </add_cat_into_db>\n");

	return (name);
}

/*
 * leggo le categorie dal database
 */
struct charlist
*leggi_cat_da_db(struct charlist *p)
{

   printf("DEBUG: <leggi_cat_da_db>\n");

   // vars
   char *daInserire;
   char *vuoto;
   struct charlist *punt = p;

   sqlite3 *db = open_db();
   char *errMsg = 0; /* pointer to error message */
   char *query = calloc (1, 2048);

   if (db == NULL) exit(1);

   char **pazResult = NULL;    /* Results of the query */
   int pnRow = 0;           /* Number of result rows written here */
   int pnColumn = 0;        /* Number of result columns written here */

   sqlite3_snprintf (1024, query, "SELECT name FROM list");
   printf ("DEBUG query: %s\n", query);

   if (sqlite3_get_table(db, query, &pazResult, &pnRow, &pnColumn, &errMsg) != SQLITE_OK) {
      fprintf(stderr, "SQL error: %s\n", errMsg);
      sqlite3_free(errMsg);
   }

   int i = 0, j = 0;
   int m = pnColumn;
   int n = pnRow;

   for (i = 1; i <= (n*m); i++) {

	   printf ("DEBUG: cat name %s;\n", pazResult[i]);
	      daInserire = strdup(pazResult[i]);

		   p = punt;
		   punt = (struct charlist *)malloc(sizeof(struct charlist));
           punt->inf = daInserire;
           punt->pun = p;

           // azzera il valore di daInserire
           daInserire = vuoto;

   }

   sqlite3_close(db);
   printf("DEBUG: </leggi_cat_da_db>\n");
   return (punt);

}


/* delete a category from db */
char
*del_cat_from_db(char *name)
{

   printf("DEBUG; <del_cat_from_db>\n");


	printf ("DEBUG: <add_cat_into_db>\n");

   sqlite3 *db = open_db();
   char *errMsg = 0; /* pointer to error message */
   char *query = calloc (1, 2048);

   if (db == NULL) exit(1);

   /* if category has some item associated we can't delete. */
   /* first control that category is empty */
   char *tmpcat = strdup(catname);
   printf ("DEBUG: tmpcat = %s\n", tmpcat);
   snprintf((char *)catname, strlen(name)+1, name);

   if (leggi_items_da_db(NULL) == NULL) {
	   printf ("DEBUG: empty cat\n");

	   /* then delete category */
	   char *query = calloc (1, 2048);
	   sqlite3_snprintf (1024, query, "DELETE FROM list WHERE name='%q'", name);
	   printf ("query = %s\n", query);

	   if ( sqlite3_exec(db, query, NULL, 0, &errMsg) != SQLITE_OK) {
	         fprintf(stderr, "SQL error: %s\n", errMsg);
	         return NULL;
	   }

   } else {

	   printf ("DEBUG: cat isn't empty\n");
	   /* or delete nothing */
	   name = NULL;
   }

   snprintf ((char *)catname, strlen(tmpcat)+1, tmpcat);
   printf ("DEBUG: catname = %s\n", catname);

   /* controlla che la categoria eliminata non sia quella in uso */
   /* if we delete 'catname' enotes fallback to "t odo" as catname */
   if (strcmp((const char*)name, catname) == 0) {
	   printf ("DEBUG: fallback to todo category\n");
	   sprintf ((char *)catname, "todo");
   }

   /* TODO - controllare che ci sia almento una categoria visualizzabile */

   printf("DEBUG; </del_cat_from_db>\n");
   return name;
}

